package db.tables;


import java.sql.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import control.ConnectionManage;
import db.parking.beans.Complaints;
import db.parking.beans.Parking;
import db.parking.beans.rates;


/**
 * AdminRates manages all requests From,To table Rates
 * @author Operations
 *
 */
public class AdminRates {
	
	private static Connection conn = ConnectionManage.getInstance().getConnection();

	public static void insertRate(rates bean) throws SQLException {
		String sql = "INSERT INTO rates (lotNo,rateType,amount) VALUES(?,?,?);";
		
		ResultSet rs = null;
        
		try (PreparedStatement stmt = conn.prepareStatement(sql)) {
			stmt.setInt(1, bean.getLotNo());
			stmt.setInt(2, bean.getRateType());
			stmt.setInt(2, bean.getAmount());
			rs = stmt.executeQuery();
			

		} catch (SQLException e) {
			System.err.println(e);
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}
	
	/**
	 * Functions returns an arrayList of all rates
	 * @return
	 * @throws SQLException
	 */
	public static ArrayList<rates> getAll() throws SQLException {
		ArrayList<rates> result=new ArrayList<rates>();
			
		String sql = "SELECT *  FROM rates";
		
		ResultSet rs = null;
        
		try (PreparedStatement stmt = conn.prepareStatement(sql)) {
			
			rs = stmt.executeQuery();
				
			while(rs.next()) {				
				rates bean = new rates();
				bean.setAmount(rs.getInt("amount"));
				bean.setRateType(rs.getInt("rateType"));
				bean.setLotNo(rs.getInt("lotNo"));
				
				result.add(bean);
			}
			return result;	

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}
	
		
	/**	Function display all rates
	 * @throws SQLException
	 */
	public static void displayAllRows() throws SQLException {

		String sql = "SELECT *  FROM rates";
		try (
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(sql);
				){

			System.out.println("Rates Table:");
			while (rs.next()) {
				StringBuffer bf = new StringBuffer();
				bf.append(rs.getInt("lotNo") + ": ");
				bf.append(rs.getString("rateType") +", ");
				bf.append(rs.getInt("amount") +", ");
				bf.append(rs.getString("duringTime"));
				System.out.println(bf.toString());
			}
		}
	}

	/**
	 * Functions returns a row of rates
	 * @param lotNo
	 * @param rateType
	 * @return
	 * @throws SQLException
	 */
	public static rates getRow(int lotNo,int rateType) throws SQLException {
//user chooses lotNo and Rate and receives back all the data
		String sql = "SELECT * FROM rates WHERE lotNo = ? AND rateType = ?";
		ResultSet rs = null;

		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			stmt.setInt(1, lotNo);
			stmt.setInt(2, rateType);
			rs = stmt.executeQuery();

			if (rs.next()) {
				rates bean = new rates();
				bean.setLotNo(lotNo);
				bean.setRateType(rateType);
				bean.setAmount(rs.getInt("amount"));
				return bean;
			} else {
				return null;
			}

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}

	

	/**
	 * @return rates array
	 * @throws SQLException
	 */
	public static rates[] getRows() throws SQLException {
		//user chooses lotNo and Rate and receives back all the data
				String sql = "SELECT * FROM rates";
				ResultSet rs = null;
				try (
						PreparedStatement stmt = conn.prepareStatement(sql);
						){
					rs = stmt.executeQuery();
					rs.last();
					int totalRows = rs.getRow();
					rs.beforeFirst();
					rates bean[] = new rates[totalRows];
					for(int i=0;i<totalRows;i++)
						bean[i] = new rates(); 
					int rowCount = 0;
					while (rs.next()) {
						bean[rowCount].setLotNo(rs.getInt("lotNo"));
						bean[rowCount].setRateType(rs.getInt("rateType"));
						bean[rowCount].setAmount(rs.getInt("amount"));
						rowCount++;
					} 
					
					return bean;

				} catch (SQLException e) {
					System.err.println(e);
					return null;
				} finally {
					if (rs != null) {
						rs.close();
					}
				}

			}
	
	/**
	 * Functions updates temporary amount set by parking manager
	 * @param bean
	 * @return
	 * @throws SQLException
	 */
	public static boolean updateTempAmount(rates bean) throws SQLException {

		String sql =
				"UPDATE rates SET " +
				"tmpAmount = ? ," +
			    "status = ? " +
				"WHERE lotNo = ? AND rateType = ?";
		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			
			stmt.setInt(1,bean.getTmpAmount());
			stmt.setInt(2,0);
			stmt.setInt(3,bean.getLotNo());
			stmt.setInt(4,bean.getRateType());
			
			int affected = stmt.executeUpdate();
			if(affected == 1){
				return true;
			}else{
				return false;
			}
			
		}
		catch(SQLException e) {
			System.err.println(e);
			return false;
		}

	}



	/**
	 * @param lotNum
	 * @return	array list of rates by lot number
	 * @throws SQLException
	 */
	public static ArrayList<rates> getByLotNum(int lotNum) throws SQLException {
		ArrayList<rates> result=new ArrayList<rates>();
		
		String sql = "SELECT *  FROM rates where lotNo = ?";
		
		ResultSet rs = null;
        
		try (PreparedStatement stmt = conn.prepareStatement(sql)) {
			stmt.setInt(1,lotNum);
			rs = stmt.executeQuery();
				
			while(rs.next()) {				
				rates bean = new rates();
				bean.setAmount(rs.getInt("amount"));
				bean.setTmpAmount(rs.getInt("tmpAmount"));
				bean.setRateType(rs.getInt("rateType"));
				bean.setLotNo(rs.getInt("lotNo"));
				bean.setRateName(rs.getString("rateName"));
				bean.setStatus(rs.getInt("status"));
				result.add(bean);
			}
			return result;	

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}
	}

	/**
	 * Functions returns all rows which were modified by the parking manager into an arrayList
	 * @return
	 * @throws SQLException
	 */
	public static ArrayList<rates> getAllModified() throws SQLException{
        ArrayList<rates> result=new ArrayList<rates>();
		// get all that not approved yet
		String sql = "SELECT *  FROM rates where status = 0";
		
		ResultSet rs = null;
        
		try (PreparedStatement stmt = conn.prepareStatement(sql)) {
			rs = stmt.executeQuery();			
			while(rs.next()) {				
				rates bean = new rates();
				bean.setAmount(rs.getInt("amount"));
				bean.setTmpAmount(rs.getInt("tmpAmount"));
				bean.setRateType(rs.getInt("rateType"));
				bean.setLotNo(rs.getInt("lotNo"));
				bean.setRateName(rs.getString("rateName"));
				bean.setStatus(rs.getInt("status"));
				result.add(bean);
			}
			return result;	

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}
	}

	public static boolean updateRateStatus(rates bean) throws SQLException{
		String sql =
				"UPDATE rates SET " +
				"status = ? ," +
				"amount = ? " +
				"WHERE lotNo = ? AND rateType = ?";
		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			
			stmt.setInt(1,bean.getStatus());
			stmt.setInt(2,bean.getTmpAmount());
			stmt.setInt(3,bean.getLotNo());
			stmt.setInt(4,bean.getRateType());
			
			int affected = stmt.executeUpdate();
			if(affected == 1){
				return true;
			}else{
				return false;
			}
			
		}
		catch(SQLException e) {
			System.err.println(e);
			return false;
		}
	}
	
}
